package com.klikmee.sms.core.web.reports;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Calendar;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import javax.sql.DataSource;

import com.klikmee.sms.core.base.dao.ProviderDAO;
import com.klikmee.sms.core.base.util.Constants;
import com.klikmee.sms.core.model.Provider;
import com.klikmee.sms.core.model.Service;
import com.klikmee.sms.core.web.reports.dto.MonthlyIncomeItem;

public class ReportingService {
	private DataSource	dataSource		= null;
	private Provider	mtsProvider		= null;	;
	private Provider	telenorProvider	= null;
	private Provider	vipProvider		= null;

	private ProviderDAO	providerDAO		= null;

	public void init() {
		this.mtsProvider = providerDAO.findByName( com.klikmee.sms.core.base.util.Constants.PROVIDER_MTS );
		this.telenorProvider = providerDAO.findByName( Constants.PROVIDER_TELENOR );
		this.vipProvider = providerDAO.findByName( Constants.PROVIDER_VIP );
	}

	public List<MonthlyIncomeItem> findMonthlyIncomeByTimeIntervalAndService( Date dateFrom, Date dateTo, Service service ) throws SQLException {
		List<MonthlyIncomeItem> result = new LinkedList<MonthlyIncomeItem>();
		
		String query = "SELECT MONTH(om.OurOutGoingTime) AS MONTH, " +
				"IF (p.id = ?, SUM(scc.MTSPrice * scc.MTSOutPercent), 0) AS MTS_Income, " +
				"IF (p.id = ?, SUM(scc.TelenorPrice * scc.TelenorOurPercent), 0) AS Telenor_Income, " +
				"IF (p.id = ?, SUM(scc.VIPPrice * scc.VipOurPercent), 0) AS VIP_Income " +
				"FROM outgoingmessage AS om " +
				"JOIN provider AS p ON (om.Provider = p.id) " +
				"JOIN paymentinvoice AS payment ON (payment.OutGoingMessageId = om.Id) " +
				"JOIN servicechargingcategory AS scc ON (payment.ServiceChargingCategoryId = scc.Id) " +
				"WHERE " +
				"om.State IN (3, 4) " +
				"AND (om.OurOutGoingTime BETWEEN ? AND ?) " +
				((service != null) ? "AND om.Service = ? " : "") +
				"GROUP BY MONTH(om.OurOutGoingTime)";

		Connection connection = this.dataSource.getConnection();

		PreparedStatement prepareStatement = connection.prepareStatement( query );
		// provider ids
		prepareStatement.setLong( 1, mtsProvider.getId() );
		prepareStatement.setLong( 2, telenorProvider.getId() );
		prepareStatement.setLong( 3, vipProvider.getId() );

		// time interval
		prepareStatement.setDate( 4, new java.sql.Date( dateFrom.getTime() ) );
		prepareStatement.setDate( 5, new java.sql.Date( dateTo.getTime() ));

		if (service != null) {
			prepareStatement.setLong( 6, service.getId() );
		}

		// execute query
		ResultSet resultSet = prepareStatement.executeQuery();
		while (resultSet.next()) {

			MonthlyIncomeItem incomeItem = new MonthlyIncomeItem();
			incomeItem.setMonth( resultSet.getInt( 1 ) );
			incomeItem.setMtsIncome( resultSet.getDouble( 2 ) );
			incomeItem.setTelenorIncome( resultSet.getDouble( 3 ) );
			incomeItem.setVipIncome( resultSet.getDouble( 4 ) );
			
			result.add( incomeItem );
		}

		connection.close();
		return result;

	}

	/**
	 * Gets calendar obj set to first day of the current month
	 * 
	 * @return
	 */
	public static Calendar getFirstDayOfTheMonth() {
		Calendar calendar = Calendar.getInstance();
		calendar.set( Calendar.DAY_OF_MONTH, 1 );
		calendar.set( Calendar.HOUR_OF_DAY, 0 );
		calendar.set( Calendar.MINUTE, 0 );
		calendar.set( Calendar.SECOND, 0 );

		return calendar;
	}

	/**
	 * Gets calendar obj set to last day of the current month
	 * 
	 * @return
	 */
	public static Calendar getLastDayOfTheMonth() {
		Calendar lastOfTheMonth = ReportingService.getFirstDayOfTheMonth();
		int lastDay = lastOfTheMonth.getActualMaximum( Calendar.DATE );
		lastOfTheMonth.set( Calendar.DAY_OF_MONTH, lastDay );

		return lastOfTheMonth;

	}

	public void setDataSource( DataSource dataSource ) {
		this.dataSource = dataSource;
	}

	public void setProviderDAO( ProviderDAO providerDAO ) {
		this.providerDAO = providerDAO;
	}

}
